package com.xinyirun.scm.core.system.mapper.query.inventory;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xinyirun.scm.bean.entity.master.inventory.MInventoryEntity;
import com.xinyirun.scm.bean.system.vo.excel.query.MInventoryDetailExportVo;
import com.xinyirun.scm.bean.system.vo.excel.query.MInventoryStagnationWarningExportVo;
import com.xinyirun.scm.bean.system.vo.master.inventory.query.MInventoryDetailQuerySumVo;
import com.xinyirun.scm.bean.system.vo.master.inventory.query.MInventoryDetailQueryVo;
import com.xinyirun.scm.common.constant.DictConstant;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * <p>
 * 库存表 Mapper 接口
 * </p>
 *
 * @author htt
 * @since 2021-09-23
 */
@Repository
public interface MInventoryDetailQueryMapper extends BaseMapper<MInventoryEntity> {

    String select_query_detail = "  "
            + "          select t1.id,                                                                                  "
            + "                 ROW_NUMBER() over(order by t1.u_time desc) AS no,                                       "
            + "                 t12.name business_name,                                                                 "
            + "                 t11.name industry_name,                                                                 "
            + "                 t10.name category_name,                                                                 "
            + "                 t1.code as inventory_code,                                                              "
            + "                 t1.warehouse_id ,                                                                       "
            + "                 t1.location_id ,                                                                        "
            + "                 t1.bin_id ,                                                                             "
            + "                 t2.name as warehouse_name,                                                              "
            + "                 t16.label warehouse_type_name,                                                          "
            + "                 t2.short_name as warehouse_short_name,                                                  "
            + "                 t3.name as location_name,                                                               "
            + "                 t3.short_name as location_short_name,                                                   "
            + "                 t4.name as bin_name,                                                                    "
            + "                 t7.id as owner_id,                                                                      "
            + "                 t7.code as owner_code,                                                                  "
            + "                 t7.name as owner_name,                                                                  "
            + "                 t7.short_name as owner_short_name,                                                      "
            + "                 t1.sku_id ,                                                                             "
            + "                 t1.sku_code ,                                                                           "
            + "                 t6.name as sku_name,                                                                    "
            + "                 t6.spec ,                                                                               "
            + "                 t6.goods_code ,                                                                         "
            + "                 t6.goods_id ,                                                                           "
            + "                 t17.name goods_prop,                                                                    "
            + "                 t6.prop_id ,                                                                            "
            + "                 t6.pm,                                                                                  "
            + "                 t5.name as unit_name,                                                                   "
            + "                 t1.lot,                                                                                 "
            + "                 t1.qty_avaible,                                                                         "
            + "                 t1.qty_lock,                                                                            "
//            + "                 ifnull( t1.qty_avaible*t13.price,0) amount,                                             "
            + "                 CASE WHEN t14.sku_id is not null THEN (t1.qty_avaible+t1.qty_lock)*t1.price             "
            + "                     ELSE (t1.qty_avaible+t1.qty_lock)*t13.price END AS amount,                          "
            + "                 CASE WHEN t14.sku_id is not null THEN t1.price ELSE t13.price END AS price,             "
            + "                 t1.u_time                                                                               "
            + "            from m_inventory t1                                                                          "
            + "       left join m_warehouse t2 on t1.warehouse_id = t2.id                                               "
            + "       left join m_location t3 on t1.location_id = t3.id                                                 "
            + "       left join m_bin t4 on t1.bin_id = t4.id                                                           "
            + "       left join s_unit t5 on t1.unit_id = t5.id                                                         "
            + "       left join m_goods_spec t6 on t1.sku_id = t6.id                                                    "
            + "       left join m_owner t7 on t1.owner_id = t7.id                                                       "
            + "       left join m_staff t8 on t1.u_id = t8.id                                                           "
            + "       left join m_goods t9 on t6.goods_id = t9.id                                                       "
            + "       left join m_category t10 on t9.category_id = t10.id                                               "
            + "       left join m_industry t11 on t10.industry_id = t11.id                                              "
            + "       left join m_business_type t12 on t11.business_id = t12.id                                         "
            + "       left join v_dict_info t16 ON t16.code = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE +"' and t16.dict_value = t2.warehouse_type"
            + "       left join(  SELECT                                                                                 "
            + "         	sku_id,                                                                                     "
            + "         	price,                                                                                      "
            + "         	price_dt,                                                                                   "
            + "         	c_time,                                                                                     "
            + "         	row_number ( ) over ( PARTITION BY sku_id ORDER BY price_dt DESC, c_time DESC ) AS row_num  "
            + "         FROM                                                                                            "
            + "         	b_goods_price                                                                               "
            + "            )t13 on t13.sku_id = t1.sku_id and t13.row_num = 1                                           "
            + "	       LEFT JOIN(     SELECT                                                                            "
            + "	            	t2.target_sku_id sku_id                                                                    "
            + "	            FROM                                                                                        "
            + "	            	b_material_convert t1                                                                   "
            + "	            	INNER JOIN b_material_convert_detail t2 ON t2.material_convert_id = t1.id               "
            + "	            WHERE                                                                                       "
            + "	            	t1.is_effective = TRUE                                                                  "
            + "	            	AND t2.is_effective = TRUE                                                              "
            + "	            GROUP BY                                                                                    "
            + "	            	t2.target_sku_id)t14 on t14.sku_id = t1.sku_id                                          "
            + "       left join m_goods_spec_prop t17 on t17.id = t6.prop_id                                            "
            + " ,(select @row_num:=0) t15                                                                               "
            ;

    String select_query_detail_sum = "  "
            + "          select t1.id,                                                                                  "
            + "                 sum(t1.qty_avaible) qty_avaible,                                                        "
            + "                 sum(t1.qty_lock) qty_lock,                                                              "
            + "                 sum(CASE WHEN t14.sku_id is not null THEN (t1.qty_avaible+t1.qty_lock)*t1.price         "
            + "                     ELSE (t1.qty_avaible+t1.qty_lock)*t13.price END) AS amount                          "
            + "            from m_inventory t1                                                                          "
            + "       left join m_warehouse t2 on t1.warehouse_id = t2.id                                               "
            + "       left join m_location t3 on t1.location_id = t3.id                                                 "
            + "       left join m_bin t4 on t1.bin_id = t4.id                                                           "
            + "       left join s_unit t5 on t1.unit_id = t5.id                                                         "
            + "       left join m_goods_spec t6 on t1.sku_id = t6.id                                                    "
            + "       left join m_owner t7 on t1.owner_id = t7.id                                                       "
            + "       left join m_staff t8 on t1.u_id = t8.id                                                           "
            + "       left join m_goods t9 on t6.goods_id = t9.id                                                       "
            + "       left join m_category t10 on t9.category_id = t10.id                                               "
            + "       left join m_industry t11 on t10.industry_id = t11.id                                              "
            + "       left join m_business_type t12 on t11.business_id = t12.id                                         "
            + "       left join(  SELECT                                                                                 "
            + "         	sku_id,                                                                                     "
            + "         	price,                                                                                      "
            + "         	price_dt,                                                                                   "
            + "         	c_time,                                                                                     "
            + "         	row_number ( ) over ( PARTITION BY sku_id ORDER BY price_dt DESC, c_time DESC ) AS row_num  "
            + "         FROM                                                                                            "
            + "         	b_goods_price                                                                               "
            + "            )t13 on t13.sku_id = t1.sku_id and t13.row_num = 1                                           "
            + "	       LEFT JOIN(     SELECT                                                                            "
            + "	            	t2.target_sku_id sku_id                                                                    "
            + "	            FROM                                                                                        "
            + "	            	b_material_convert t1                                                                   "
            + "	            	INNER JOIN b_material_convert_detail t2 ON t2.material_convert_id = t1.id               "
            + "	            WHERE                                                                                       "
            + "	            	t1.is_effective = TRUE                                                                  "
            + "	            	AND t2.is_effective = TRUE                                                              "
            + "	            GROUP BY                                                                                    "
            + "	            	t2.target_sku_id)t14 on t14.sku_id = t1.sku_id                                          "
            + "       left join m_goods_spec_prop t17 on t17.id = t6.prop_id                                            "
            ;


    String select_query_detail_warning = "  "
            + "          select t1.id,                                                                                  "
            + "                 ROW_NUMBER() over(order by t1.u_time desc) AS no,                                       "
            + "                 t12.name business_name,                                                                 "
            + "                 t11.name industry_name,                                                                 "
            + "                 t10.name category_name,                                                                 "
            + "                 t1.code as inventory_code,                                                              "
            + "                 t1.warehouse_id ,                                                                       "
            + "                 t1.location_id ,                                                                        "
            + "                 t1.bin_id ,                                                                             "
            + "                 t2.name as warehouse_name,                                                              "
            + "                 t16.label warehouse_type_name,                                                          "
            + "                 t2.short_name as warehouse_short_name,                                                  "
            + "                 t3.name as location_name,                                                               "
            + "                 t3.short_name as location_short_name,                                                   "
            + "                 t4.name as bin_name,                                                                    "
            + "                 t7.id as owner_id,                                                                      "
            + "                 t7.code as owner_code,                                                                  "
            + "                 t7.name as owner_name,                                                                  "
            + "                 t7.short_name as owner_short_name,                                                      "
            + "                 t1.sku_id ,                                                                             "
            + "                 t1.sku_code ,                                                                           "
            + "                 t6.name as sku_name,                                                                    "
            + "                 t6.spec ,                                                                               "
            + "                 t6.goods_code ,                                                                         "
            + "                 t6.goods_id ,                                                                           "
            + "                 t17.name goods_prop,                                                                    "
            + "                 t6.prop_id ,                                                                            "
            + "                 t6.pm,                                                                                  "
            + "                 t5.name as unit_name,                                                                   "
            + "                 t1.lot,                                                                                 "
            + "                 t1.qty_avaible,                                                                         "
            + "                 t1.qty_lock,                                                                            "
//            + "                 ifnull( t1.qty_avaible*t13.price,0) amount,                                             "
            + "                 CASE WHEN t14.sku_id is not null THEN (t1.qty_avaible+t1.qty_lock)*t1.price             "
            + "                     ELSE (t1.qty_avaible+t1.qty_lock)*t13.price END AS amount,                          "
            + "                 CASE WHEN t14.sku_id is not null THEN t1.price ELSE t13.price END AS price,             "
            + "                 t1.u_time,                                                                               "
            + "                 t18.u_time  as  in_time,                                                                "
            + "                 t19.u_time  as  out_time,                                                               "
            + "                 t20.u_time  as  up_time                                                                "
            + "            from m_inventory t1                                                                          "
            + "       left join m_warehouse t2 on t1.warehouse_id = t2.id                                               "
            + "       left join m_location t3 on t1.location_id = t3.id                                                 "
            + "       left join m_bin t4 on t1.bin_id = t4.id                                                           "
            + "       left join s_unit t5 on t1.unit_id = t5.id                                                         "
            + "       left join m_goods_spec t6 on t1.sku_id = t6.id                                                    "
            + "       left join m_owner t7 on t1.owner_id = t7.id                                                       "
            + "       left join m_staff t8 on t1.u_id = t8.id                                                           "
            + "       left join m_goods t9 on t6.goods_id = t9.id                                                       "
            + "       left join m_category t10 on t9.category_id = t10.id                                               "
            + "       left join m_industry t11 on t10.industry_id = t11.id                                              "
            + "       left join m_business_type t12 on t11.business_id = t12.id                                         "
            + "       left join v_dict_info t16 ON t16.code = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE +"' and t16.dict_value = t2.warehouse_type"
            + "       left join(  SELECT                                                                                 "
            + "         	sku_id,                                                                                     "
            + "         	price,                                                                                      "
            + "         	price_dt,                                                                                   "
            + "         	c_time,                                                                                     "
            + "         	row_number ( ) over ( PARTITION BY sku_id ORDER BY price_dt DESC, c_time DESC ) AS row_num  "
            + "         FROM                                                                                            "
            + "         	b_goods_price                                                                               "
            + "            )t13 on t13.sku_id = t1.sku_id and t13.row_num = 1                                           "
            + "	       LEFT JOIN(     SELECT                                                                            "
            + "	            	t2.target_sku_id sku_id                                                                    "
            + "	            FROM                                                                                        "
            + "	            	b_material_convert t1                                                                   "
            + "	            	INNER JOIN b_material_convert_detail t2 ON t2.material_convert_id = t1.id               "
            + "	            WHERE                                                                                       "
            + "	            	t1.is_effective = TRUE                                                                  "
            + "	            	AND t2.is_effective = TRUE                                                              "
            + "	            GROUP BY                                                                                    "
            + "	            	t2.target_sku_id)t14 on t14.sku_id = t1.sku_id                                          "
            + "       left join m_goods_spec_prop t17 on t17.id = t6.prop_id                                            "
            + "  left join(select max(u_time) as u_time,warehouse_id,sku_id,owner_id from b_in where status in          "
            + "   ("+DictConstant.DICT_B_IN_STATUS_TWO+","+DictConstant.DICT_B_IN_STATUS_TWO+")                "
            + "     group by warehouse_id,sku_id,owner_id) t18  on  t18.warehouse_id = t1.warehouse_id                  "
            + "       and t18.sku_id =  t1.sku_id and t18.owner_id = t1.owner_id                                        "
            + "  left join(select max(u_time) as u_time,warehouse_id,sku_id,owner_id from b_out where status in         "
//            + "   ("+DictConstant.DICT_B_OUT_STATUS_SUBMITTED+","+DictConstant.DICT_B_OUT_STATUS_PASSED+")                "
            + "     group by warehouse_id,sku_id,owner_id) t19 on t19.warehouse_id = t1.warehouse_id                    "
            + "  and t19.sku_id = t1.sku_id and t19.owner_id = t1.owner_id                                              "
            + "  left join(SELECT max( t1.u_time ) AS u_time,t1.warehouse_id,t1.sku_id,t2.owner_id                      "
            + "      FROM b_adjust_detail t1 LEFT JOIN b_adjust t2 ON t1.adjust_id = t2.id                              "
            + "      WHERE t1.status = "+DictConstant.DICT_B_ADJUST_STATUS_PASSED+"                                     "
            + "      GROUP BY t1.warehouse_id,t1.sku_id,t2.owner_id) t20  on t20.warehouse_id = t1.warehouse_id         "
            + "  and t20.sku_id = t1.sku_id and t20.owner_id = t1.owner_id                                              "
            + " ,(select @row_num:=0) t15                                                                               "
            ;

    /**
     * 库存明细查询sql
     */
    @Select("  <script>  "
            + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
            + select_query_detail
            + "  where true                                                                                                                  "
            + "    and (CONCAT(t7.name,t7.short_name,t7.name_pinyin,t7.short_name_pinyin) like CONCAT ('%',#{p1.owner_name,jdbcType=VARCHAR},'%') or #{p1.owner_name,jdbcType=VARCHAR} is null)        "
            + "    and (CONCAT(ifnull(t6.name,''),ifnull(t6.spec,''),ifnull(t6.pm,''),ifnull(t9.name,'')) like CONCAT ('%',#{p1.sku_name,jdbcType=VARCHAR},'%') or #{p1.sku_name,jdbcType=VARCHAR} is null)                                        "
            + "    and (concat(t9.code, '_', t6.code) like concat('%', #{p1.sku_code}, '%') or #{p1.sku_code} is null or #{p1.sku_code} = '')                                                        "
            + "   <if test='p1.warehouse_ids != null and p1.warehouse_ids.length!=0' >                                                                                                                 "
            + "    and t1.warehouse_id in                                                                                                                                                              "
            + "        <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                              "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "    and (t1.warehouse_id = #{p1.warehouse_id,jdbcType=INTEGER} or #{p1.warehouse_id,jdbcType=INTEGER} is null)                                                                          "
            + "    and (t1.owner_id = #{p1.owner_id,jdbcType=INTEGER} or #{p1.owner_id,jdbcType=INTEGER} is null)                                                                                      "
            + "    and (t10.name like concat('%', #{p1.category_name,jdbcType=VARCHAR}, '%') or #{p1.category_name,jdbcType=VARCHAR} is null or #{p1.category_name,jdbcType=VARCHAR} = '')             "
            + "    and (t17.name like concat('%', #{p1.goods_prop,jdbcType=VARCHAR}, '%') or #{p1.goods_prop,jdbcType=VARCHAR} is null or #{p1.goods_prop,jdbcType=VARCHAR} = '')             "
            + "   <if test='p1.owner_ids != null and p1.owner_ids.length != 0' >                                                                                                                       "
            + "    and t1.owner_id in                                                                                                                                                                  "
            + "        <foreach collection='p1.owner_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                                  "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "   <if test='p1.warehouse_types != null and p1.warehouse_types.length != 0' >                                                                                                           "
            + "    and t2.warehouse_type in                                                                                                                                                            "
            + "        <foreach collection='p1.warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                                            "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "   <if test='p1.isZLEnvironment' >                                                                                                                                                      "
            + "     and t2.warehouse_type != '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_CL+"'                                                                                                                                                  "
            + "   </if>                                                                                                                                                                                "
            // 仓库权限
            + "     ${p1.params.dataScopeAnnotation}                                                                                                "
            + "  </script>   ")
    IPage<MInventoryDetailQueryVo> queryInventoryDetails(Page page, @Param("p1") MInventoryDetailQueryVo searchCondition);

    /**
     * 库存明细查询sql
     */
    @Select("  <script>  "
            + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
            + select_query_detail_sum
            + "  where true                                                                                                                  "
            + "    and (CONCAT(t7.name,t7.short_name,t7.name_pinyin,t7.short_name_pinyin) like CONCAT ('%',#{p1.owner_name,jdbcType=VARCHAR},'%') or #{p1.owner_name,jdbcType=VARCHAR} is null)        "
            + "    and (CONCAT(ifnull(t6.name,''),ifnull(t6.spec,''),ifnull(t6.pm,''),ifnull(t9.name,'')) like CONCAT ('%',#{p1.sku_name,jdbcType=VARCHAR},'%') or #{p1.sku_name,jdbcType=VARCHAR} is null)                                        "
            + "    and (concat(t9.code, '_', t6.code) like concat('%', #{p1.sku_code}, '%') or #{p1.sku_code} is null or #{p1.sku_code} = '')                                                        "
            //            + "    and (t1.warehouse_id = #{p1.warehouse_id,jdbcType=INTEGER} or #{p1.warehouse_id,jdbcType=INTEGER}  is null    )                                                       "
            + "   <if test='p1.warehouse_ids != null and p1.warehouse_ids.length!=0' >                                                                                                                 "
            + "    and t1.warehouse_id in                                                                                                                                                              "
            + "        <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                              "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "    and (t1.warehouse_id = #{p1.warehouse_id,jdbcType=INTEGER} or #{p1.warehouse_id,jdbcType=INTEGER} is null)                                                                          "
            + "    and (t1.owner_id = #{p1.owner_id,jdbcType=INTEGER} or #{p1.owner_id,jdbcType=INTEGER} is null)                                                                                      "
            + "    and (t10.name like concat('%', #{p1.category_name,jdbcType=VARCHAR}, '%') or #{p1.category_name,jdbcType=VARCHAR} is null or #{p1.category_name,jdbcType=VARCHAR} = '')             "
            + "    and (t17.name like concat('%', #{p1.goods_prop,jdbcType=VARCHAR}, '%') or #{p1.goods_prop,jdbcType=VARCHAR} is null or #{p1.goods_prop,jdbcType=VARCHAR} = '')             "
            + "   <if test='p1.owner_ids != null and p1.owner_ids.length != 0' >                                                                                                                       "
            + "    and t1.owner_id in                                                                                                                                                                  "
            + "        <foreach collection='p1.owner_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                                  "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "   <if test='p1.warehouse_types != null and p1.warehouse_types.length != 0' >                                                                                                           "
            + "    and t2.warehouse_type in                                                                                                                                                            "
            + "        <foreach collection='p1.warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                                            "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "   <if test='p1.isZLEnvironment' >                                                                                                                                                      "
            + "     and t2.warehouse_type != '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_CL+"'                                                                                                                                                  "
            + "   </if>                                                                                                                                                                                "
            // 仓库权限
            + "     ${p1.params.dataScopeAnnotation}                                                                                                "
            + "  </script>   ")
    MInventoryDetailQuerySumVo selectSumData(@Param("p1") MInventoryDetailQueryVo searchCondition);

    /**
     * 页面查询列表
     * @param searchCondition
     * @return
     */
    @Select("   <script>                                                                                        "
            + select_query_detail
            + "  where true                                                                                     "
            + "   <if test='p1 != null and p1.size!=0' >                                                        "
            + "    and t1.id in                                                                                 "
            + "        <foreach collection='p1' item='item' index='index' open='(' separator=',' close=')'>     "
            + "         #{item.id,jdbcType=INTEGER}                                                             "
            + "        </foreach>                                                                               "
            + "   </if>                                                                                                                                                  "
            + "  </script>    ")
    List<MInventoryDetailExportVo> selectExportList(@Param("p1") List<MInventoryDetailQueryVo> searchCondition);

    /**
     * 库存明细查询sql
     */
    @Select("  <script>  "
            + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
            + select_query_detail
            + "  where true                                                                                                                  "
            + "    and (CONCAT(t7.name,t7.short_name,t7.name_pinyin,t7.short_name_pinyin) like CONCAT ('%',#{p1.owner_name,jdbcType=VARCHAR},'%') or #{p1.owner_name,jdbcType=VARCHAR} is null)        "
            + "    and (CONCAT(ifnull(t6.name,''),ifnull(t6.spec,''),ifnull(t6.pm,''),ifnull(t9.name,'')) like CONCAT ('%',#{p1.sku_name,jdbcType=VARCHAR},'%') or #{p1.sku_name,jdbcType=VARCHAR} is null)                                        "
            + "    and (concat(t9.code, '_', t6.code) like concat('%', #{p1.sku_code}, '%') or #{p1.sku_code} is null or #{p1.sku_code} = '')                                                        "
            + "   <if test='p1.warehouse_ids != null and p1.warehouse_ids.length!=0' >                                                                                                                 "
            + "    and t1.warehouse_id in                                                                                                                                                              "
            + "        <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                              "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "    and (t1.warehouse_id = #{p1.warehouse_id,jdbcType=INTEGER} or #{p1.warehouse_id,jdbcType=INTEGER} is null)                                                                          "
            + "    and (t1.owner_id = #{p1.owner_id,jdbcType=INTEGER} or #{p1.owner_id,jdbcType=INTEGER} is null)                                                                                      "
            + "    and (t10.name like concat('%', #{p1.category_name,jdbcType=VARCHAR}, '%') or #{p1.category_name,jdbcType=VARCHAR} is null or #{p1.category_name,jdbcType=VARCHAR} = '')             "
            + "    and (t17.name like concat('%', #{p1.goods_prop,jdbcType=VARCHAR}, '%') or #{p1.goods_prop,jdbcType=VARCHAR} is null or #{p1.goods_prop,jdbcType=VARCHAR} = '')             "
            + "   <if test='p1.owner_ids != null and p1.owner_ids.length != 0' >                                                                                                                       "
            + "    and t1.owner_id in                                                                                                                                                                  "
            + "        <foreach collection='p1.owner_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                                  "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "   <if test='p1.warehouse_types != null and p1.warehouse_types.length != 0' >                                                                                                           "
            + "    and t2.warehouse_type in                                                                                                                                                            "
            + "        <foreach collection='p1.warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                                            "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "   <if test='p1.isZLEnvironment' >                                                                                                                                                      "
            + "     and t2.warehouse_type != '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_CL+"'                                                                                                                                                  "
            + "   </if>                                                                                                                                                                                "
            // 仓库权限
            + "     ${p1.params.dataScopeAnnotation}                                                                                                "
            + "  </script>   ")
    List<MInventoryDetailExportVo> selectExportAllList(@Param("p1") MInventoryDetailQueryVo searchCondition);

    /**
     * 导出条数查询
     * @param searchCondition
     * @return
     */
    @Select("  <script>  "
            + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
            + "          select count(1)                                                                                "
            + "            from m_inventory t1                                                                          "
            + "       left join m_warehouse t2 on t1.warehouse_id = t2.id                                               "
            + "       left join m_goods_spec t6 on t1.sku_id = t6.id                                                    "
            + "       left join m_owner t7 on t1.owner_id = t7.id                                                       "
            + "       left join m_goods t9 on t6.goods_id = t9.id                                                       "
            + "       left join m_category t10 on t9.category_id = t10.id                                               "
            + "       left join m_goods_spec_prop t17 on t17.id = t6.prop_id                                            "
            + "  where true                                                                                                                  "
            + "    and (CONCAT(t7.name,t7.short_name,t7.name_pinyin,t7.short_name_pinyin) like CONCAT ('%',#{p1.owner_name,jdbcType=VARCHAR},'%') or #{p1.owner_name,jdbcType=VARCHAR} is null)        "
            + "    and (CONCAT(ifnull(t6.name,''),ifnull(t6.spec,''),ifnull(t6.pm,''),ifnull(t9.name,'')) like CONCAT ('%',#{p1.sku_name,jdbcType=VARCHAR},'%') or #{p1.sku_name,jdbcType=VARCHAR} is null)                                        "
            + "    and (concat(t9.code, '_', t6.code) like concat('%', #{p1.sku_code}, '%') or #{p1.sku_code} is null or #{p1.sku_code} = '')                                                        "
            + "   <if test='p1.warehouse_ids != null and p1.warehouse_ids.length!=0' >                                                                                                                 "
            + "    and t1.warehouse_id in                                                                                                                                                              "
            + "        <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                              "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "    and (t1.warehouse_id = #{p1.warehouse_id,jdbcType=INTEGER} or #{p1.warehouse_id,jdbcType=INTEGER} is null)                                                                          "
            + "    and (t1.owner_id = #{p1.owner_id,jdbcType=INTEGER} or #{p1.owner_id,jdbcType=INTEGER} is null)                                                                                      "
            + "    and (t10.name like concat('%', #{p1.category_name,jdbcType=VARCHAR}, '%') or #{p1.category_name,jdbcType=VARCHAR} is null or #{p1.category_name,jdbcType=VARCHAR} = '')             "
            + "    and (t17.name like concat('%', #{p1.goods_prop,jdbcType=VARCHAR}, '%') or #{p1.goods_prop,jdbcType=VARCHAR} is null or #{p1.goods_prop,jdbcType=VARCHAR} = '')                      "
            + "   <if test='p1.owner_ids != null and p1.owner_ids.length != 0' >                                                                                                                       "
            + "    and t1.owner_id in                                                                                                                                                                  "
            + "        <foreach collection='p1.owner_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                                  "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "   <if test='p1.warehouse_types != null and p1.warehouse_types.length != 0' >                                                                                                           "
            + "    and t2.warehouse_type in                                                                                                                                                            "
            + "        <foreach collection='p1.warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                                            "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "   <if test='p1.isZLEnvironment' >                                                                                                                                                      "
            + "     and t2.warehouse_type != '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_CL+"'                                                                                                                                                  "
            + "   </if>                                                                                                                                                                                "

            // 仓库权限
            + "     ${p1.params.dataScopeAnnotation}                                                                                                "
            + "  </script>   ")
    int selectExportNum(@Param("p1") MInventoryDetailQueryVo searchCondition);

    @Select("  <script>  "
            + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
            + "          select t1.id,                                                                                  "
            + "                 t12.name business_name,                                                                 "
            + "                 t11.name industry_name,                                                                 "
            + "                 t10.name category_name,                                                                 "
            + "                 t1.code as inventory_code,                                                              "
            + "                 t1.warehouse_id ,                                                                       "
            + "                 t1.location_id ,                                                                        "
            + "                 t1.bin_id ,                                                                             "
            + "                 t2.name as warehouse_name,                                                              "
            + "                 t16.label warehouse_type_name,                                                          "
            + "                 t2.short_name as warehouse_short_name,                                                  "
            + "                 t3.name as location_name,                                                               "
            + "                 t3.short_name as location_short_name,                                                   "
            + "                 t4.name as bin_name,                                                                    "
            + "                 t7.id as owner_id,                                                                      "
            + "                 t7.code as owner_code,                                                                  "
            + "                 t7.name as owner_name,                                                                  "
            + "                 t7.short_name as owner_short_name,                                                      "
            + "                 t1.sku_id ,                                                                             "
            + "                 t1.sku_code ,                                                                           "
            + "                 t6.name as sku_name,                                                                    "
            + "                 t6.spec ,                                                                               "
            + "                 t6.goods_code ,                                                                         "
            + "                 t6.goods_id ,                                                                           "
            + "                 t17.name goods_prop,                                                                    "
            + "                 t6.prop_id ,                                                                            "
            + "                 t6.pm,                                                                                  "
            + "                 t5.name as unit_name,                                                                   "
            + "                 t1.lot,                                                                                 "
            + "                 t1.qty_avaible,                                                                         "
            + "                 t1.qty_lock,                                                                            "
            + "                 CASE WHEN t14.sku_id is not null THEN (t1.qty_avaible+t1.qty_lock)*t1.price             "
            + "                     ELSE (t1.qty_avaible+t1.qty_lock)*t13.price END AS amount,                          "
            + "                 CASE WHEN t14.sku_id is not null THEN t1.price ELSE t13.price END AS price,             "
            + "                 t1.u_time                                                                               "
            + "            from m_inventory t1                                                                          "
            + "       left join m_warehouse t2 on t1.warehouse_id = t2.id                                               "
            + "       left join m_location t3 on t1.location_id = t3.id                                                 "
            + "       left join m_bin t4 on t1.bin_id = t4.id                                                           "
            + "       left join s_unit t5 on t1.unit_id = t5.id                                                         "
            + "       left join m_goods_spec t6 on t1.sku_id = t6.id                                                    "
            + "       left join m_owner t7 on t1.owner_id = t7.id                                                       "
            + "       left join m_staff t8 on t1.u_id = t8.id                                                           "
            + "       left join m_goods t9 on t6.goods_id = t9.id                                                       "
            + "       left join m_category t10 on t9.category_id = t10.id                                               "
            + "       left join m_industry t11 on t10.industry_id = t11.id                                              "
            + "       left join m_business_type t12 on t11.business_id = t12.id                                         "
            + "       left join v_dict_info t16 ON t16.code = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE +"' and t16.dict_value = t2.warehouse_type"
            + "       left join(  SELECT                                                                                 "
            + "         	sku_id,                                                                                     "
            + "         	price,                                                                                      "
            + "         	price_dt,                                                                                   "
            + "         	c_time,                                                                                     "
            + "         	row_number ( ) over ( PARTITION BY sku_id ORDER BY price_dt DESC, c_time DESC ) AS row_num  "
            + "         FROM                                                                                            "
            + "         	b_goods_price                                                                               "
            + "            )t13 on t13.sku_id = t1.sku_id and t13.row_num = 1                                           "
            + "	       LEFT JOIN(     SELECT                                                                            "
            + "	            	t2.target_sku_id sku_id                                                                    "
            + "	            FROM                                                                                        "
            + "	            	b_material_convert t1                                                                   "
            + "	            	INNER JOIN b_material_convert_detail t2 ON t2.material_convert_id = t1.id               "
            + "	            WHERE                                                                                       "
            + "	            	t1.is_effective = TRUE                                                                  "
            + "	            	AND t2.is_effective = TRUE                                                              "
            + "	            GROUP BY                                                                                    "
            + "	            	t2.target_sku_id)t14 on t14.sku_id = t1.sku_id                                          "
            + "       left join m_goods_spec_prop t17 on t17.id = t6.prop_id                                            "

            + "  where true                                                                                                                  "
            + "    and (CONCAT(t7.name,t7.short_name,t7.name_pinyin,t7.short_name_pinyin) like CONCAT ('%',#{p1.owner_name,jdbcType=VARCHAR},'%') or #{p1.owner_name,jdbcType=VARCHAR} is null)        "
            + "    and (CONCAT(ifnull(t6.name,''),ifnull(t6.spec,''),ifnull(t6.pm,''),ifnull(t9.name,'')) like CONCAT ('%',#{p1.sku_name,jdbcType=VARCHAR},'%') or #{p1.sku_name,jdbcType=VARCHAR} is null)                                        "
            + "    and (concat(t9.code, '_', t6.code) like concat('%', #{p1.sku_code}, '%') or #{p1.sku_code} is null or #{p1.sku_code} = '')                                                        "
            + "   <if test='p1.warehouse_ids != null and p1.warehouse_ids.length!=0' >                                                                                                                 "
            + "    and t1.warehouse_id in                                                                                                                                                              "
            + "        <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                              "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "    and (t1.warehouse_id = #{p1.warehouse_id,jdbcType=INTEGER} or #{p1.warehouse_id,jdbcType=INTEGER} is null)                                                                          "
            + "    and (t1.owner_id = #{p1.owner_id,jdbcType=INTEGER} or #{p1.owner_id,jdbcType=INTEGER} is null)                                                                                      "
            + "    and (t10.name like concat('%', #{p1.category_name,jdbcType=VARCHAR}, '%') or #{p1.category_name,jdbcType=VARCHAR} is null or #{p1.category_name,jdbcType=VARCHAR} = '')             "
            + "    and (t17.name like concat('%', #{p1.goods_prop,jdbcType=VARCHAR}, '%') or #{p1.goods_prop,jdbcType=VARCHAR} is null or #{p1.goods_prop,jdbcType=VARCHAR} = '')             "
            + "   <if test='p1.owner_ids != null and p1.owner_ids.length != 0' >                                                                                                                       "
            + "    and t1.owner_id in                                                                                                                                                                  "
            + "        <foreach collection='p1.owner_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                                  "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "   <if test='p1.warehouse_types != null and p1.warehouse_types.length != 0' >                                                                                                           "
            + "    and t2.warehouse_type in                                                                                                                                                            "
            + "        <foreach collection='p1.warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                                            "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "   <if test='p1.isZLEnvironment' >                                                                                                                                                      "
            + "     and t2.warehouse_type != '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_CL+"'                                                                                                                                                  "
            + "   </if>                                                                                                                                                                                "
            // 仓库权限
            + "     ${p1.params.dataScopeAnnotation}                                                                                                "
            + "  </script>   ")
    IPage<MInventoryDetailQueryVo> selectListByOrderId(Page<MInventoryDetailQueryVo> pageCondition,@Param("p1") MInventoryDetailQueryVo searchCondition);


    /**
     * 中转停滞预警明细
     */
    @Select("  <script>  "
            + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
            + select_query_detail_warning
            + "  where true                                                                                                                  "
            + "    and (CONCAT(t7.name,t7.short_name,t7.name_pinyin,t7.short_name_pinyin) like CONCAT ('%',#{p1.owner_name,jdbcType=VARCHAR},'%') or #{p1.owner_name,jdbcType=VARCHAR} is null)        "
            + "    and (CONCAT(ifnull(t6.name,''),ifnull(t6.spec,''),ifnull(t6.pm,''),ifnull(t9.name,'')) like CONCAT ('%',#{p1.sku_name,jdbcType=VARCHAR},'%') or #{p1.sku_name,jdbcType=VARCHAR} is null)                                        "
            + "    and (concat(t9.code, '_', t6.code) like concat('%', #{p1.sku_code}, '%') or #{p1.sku_code} is null or #{p1.sku_code} = '')                                                        "
            + "   <if test='p1.warehouse_ids != null and p1.warehouse_ids.length!=0' >                                                                                                                 "
            + "    and t1.warehouse_id in                                                                                                                                                              "
            + "        <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                              "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "    and (t1.warehouse_id = #{p1.warehouse_id,jdbcType=INTEGER} or #{p1.warehouse_id,jdbcType=INTEGER} is null)                                                                          "
            + "    and (t1.owner_id = #{p1.owner_id,jdbcType=INTEGER} or #{p1.owner_id,jdbcType=INTEGER} is null)                                                                                      "
            + "    and (t10.name like concat('%', #{p1.category_name,jdbcType=VARCHAR}, '%') or #{p1.category_name,jdbcType=VARCHAR} is null or #{p1.category_name,jdbcType=VARCHAR} = '')             "
            + "    and (t17.name like concat('%', #{p1.goods_prop,jdbcType=VARCHAR}, '%') or #{p1.goods_prop,jdbcType=VARCHAR} is null or #{p1.goods_prop,jdbcType=VARCHAR} = '')             "
            +"     and (t1.id = #{p1.id,jdbcType=INTEGER} or #{p1.id,jdbcType=INTEGER} is null)                                       "
            + "   <if test='p1.owner_ids != null and p1.owner_ids.length != 0' >                                                                                                                       "
            + "    and t1.owner_id in                                                                                                                                                                  "
            + "        <foreach collection='p1.owner_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                                  "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "   <if test='p1.warehouse_types != null and p1.warehouse_types.length != 0' >                                                                                                           "
            + "    and t2.warehouse_type in                                                                                                                                                            "
            + "        <foreach collection='p1.warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                                            "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "   <if test='p1.isZLEnvironment' >                                                                                                                                                      "
            + "     and t2.warehouse_type != '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_CL+"'                                                                                                                                                  "
            + "   </if>                                                                                                                                                                                "

            +" 	AND exists (  SELECT  1 FROM b_message tt1 where                                                   "
            +"       tt1.serial_type = '"+DictConstant.DICT_SYS_CODE_TYPE_M_INVENTORY_STAGNATION+"'                "
            +"       and tt1.type = "+DictConstant.DICT_B_MESSAGE_TYPE_1+"                                       "
            +"       and t1.id = tt1.serial_id and t1.code = tt1.serial_code)                                       "
            // 仓库权限
            + "     ${p1.params.dataScopeAnnotation}                                                                                                "
            + "  </script>   ")
    IPage<MInventoryDetailQueryVo> queryInventoryByWarning(Page<MInventoryDetailQueryVo> pageCondition,@Param("p1")  MInventoryDetailQueryVo searchCondition);

    /**
     * 中转停滞预警统计
     */
    @Select("  <script>  "
            + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
            + select_query_detail_sum
            + "  where true                                                                                                                  "
            + "    and (CONCAT(t7.name,t7.short_name,t7.name_pinyin,t7.short_name_pinyin) like CONCAT ('%',#{p1.owner_name,jdbcType=VARCHAR},'%') or #{p1.owner_name,jdbcType=VARCHAR} is null)        "
            + "    and (CONCAT(ifnull(t6.name,''),ifnull(t6.spec,''),ifnull(t6.pm,''),ifnull(t9.name,'')) like CONCAT ('%',#{p1.sku_name,jdbcType=VARCHAR},'%') or #{p1.sku_name,jdbcType=VARCHAR} is null)                                        "
            + "    and (concat(t9.code, '_', t6.code) like concat('%', #{p1.sku_code}, '%') or #{p1.sku_code} is null or #{p1.sku_code} = '')                                                        "
            //            + "    and (t1.warehouse_id = #{p1.warehouse_id,jdbcType=INTEGER} or #{p1.warehouse_id,jdbcType=INTEGER}  is null    )                                                       "
            + "   <if test='p1.warehouse_ids != null and p1.warehouse_ids.length!=0' >                                                                                                                 "
            + "    and t1.warehouse_id in                                                                                                                                                              "
            + "        <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                              "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "    and (t1.warehouse_id = #{p1.warehouse_id,jdbcType=INTEGER} or #{p1.warehouse_id,jdbcType=INTEGER} is null)                                                                          "
            + "    and (t1.owner_id = #{p1.owner_id,jdbcType=INTEGER} or #{p1.owner_id,jdbcType=INTEGER} is null)                                                                                      "
            + "    and (t10.name like concat('%', #{p1.category_name,jdbcType=VARCHAR}, '%') or #{p1.category_name,jdbcType=VARCHAR} is null or #{p1.category_name,jdbcType=VARCHAR} = '')             "
            + "    and (t17.name like concat('%', #{p1.goods_prop,jdbcType=VARCHAR}, '%') or #{p1.goods_prop,jdbcType=VARCHAR} is null or #{p1.goods_prop,jdbcType=VARCHAR} = '')             "
            +"     and (t1.id = #{p1.id,jdbcType=INTEGER} or #{p1.id,jdbcType=INTEGER} is null)                                       "
            + "   <if test='p1.owner_ids != null and p1.owner_ids.length != 0' >                                                                                                                       "
            + "    and t1.owner_id in                                                                                                                                                                  "
            + "        <foreach collection='p1.owner_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                                  "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "   <if test='p1.warehouse_types != null and p1.warehouse_types.length != 0' >                                                                                                           "
            + "    and t2.warehouse_type in                                                                                                                                                            "
            + "        <foreach collection='p1.warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                                            "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "   <if test='p1.isZLEnvironment' >                                                                                                                                                      "
            + "     and t2.warehouse_type != '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_CL+"'                                                                                                                                                  "
            + "   </if>                                                                                                                                                                                "
            +" 	AND exists ( SELECT  1 FROM b_message tt1 where                                                   "
            +"       tt1.serial_type = '"+DictConstant.DICT_SYS_CODE_TYPE_M_INVENTORY_STAGNATION+"'                "
            +"       and tt1.type = "+DictConstant.DICT_B_MESSAGE_TYPE_1+"                                       "
            +"       and t1.id = tt1.serial_id and t1.code = tt1.serial_code)                                       "
            // 仓库权限
            + "     ${p1.params.dataScopeAnnotation}                                                                                                "
            + "  </script>   ")
    MInventoryDetailQuerySumVo queryInventoryByWarningSum(@Param("p1")MInventoryDetailQueryVo searchCondition);

    /**
     * 中转滞留预警页面导出
     */
    @Select("   <script>                                                                                        "
            + select_query_detail_warning
            + "  where true                                                                                     "
            + "   <if test='p1 != null and p1.size!=0' >                                                        "
            + "    and t1.id in                                                                                 "
            + "        <foreach collection='p1' item='item' index='index' open='(' separator=',' close=')'>     "
            + "         #{item.id,jdbcType=INTEGER}                                                             "
            + "        </foreach>                                                                               "
            + "   </if>                                                                                                                                                  "
            + "  </script>    ")
    List<MInventoryStagnationWarningExportVo> selectExportDataWarning(@Param("p1") List<MInventoryDetailQueryVo> searchCondition);

    /**
     * 中转滞留预警导出条数查询
     */
    @Select("  <script>  "
            + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
            + "          select count(1)                                                                                "
            + "            from m_inventory t1                                                                          "
            + "       left join m_warehouse t2 on t1.warehouse_id = t2.id                                               "
            + "       left join m_goods_spec t6 on t1.sku_id = t6.id                                                    "
            + "       left join m_owner t7 on t1.owner_id = t7.id                                                       "
            + "       left join m_goods t9 on t6.goods_id = t9.id                                                       "
            + "       left join m_category t10 on t9.category_id = t10.id                                               "
            + "       left join m_goods_spec_prop t17 on t17.id = t6.prop_id                                            "
            + "  where true                                                                                                                  "
            + "    and (CONCAT(t7.name,t7.short_name,t7.name_pinyin,t7.short_name_pinyin) like CONCAT ('%',#{p1.owner_name,jdbcType=VARCHAR},'%') or #{p1.owner_name,jdbcType=VARCHAR} is null)        "
            + "    and (CONCAT(ifnull(t6.name,''),ifnull(t6.spec,''),ifnull(t6.pm,''),ifnull(t9.name,'')) like CONCAT ('%',#{p1.sku_name,jdbcType=VARCHAR},'%') or #{p1.sku_name,jdbcType=VARCHAR} is null)                                        "
            + "    and (concat(t9.code, '_', t6.code) like concat('%', #{p1.sku_code}, '%') or #{p1.sku_code} is null or #{p1.sku_code} = '')                                                        "
            + "   <if test='p1.warehouse_ids != null and p1.warehouse_ids.length!=0' >                                                                                                                 "
            + "    and t1.warehouse_id in                                                                                                                                                              "
            + "        <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                              "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "    and (t1.warehouse_id = #{p1.warehouse_id,jdbcType=INTEGER} or #{p1.warehouse_id,jdbcType=INTEGER} is null)                                                                          "
            + "    and (t1.owner_id = #{p1.owner_id,jdbcType=INTEGER} or #{p1.owner_id,jdbcType=INTEGER} is null)                                                                                      "
            + "    and (t10.name like concat('%', #{p1.category_name,jdbcType=VARCHAR}, '%') or #{p1.category_name,jdbcType=VARCHAR} is null or #{p1.category_name,jdbcType=VARCHAR} = '')             "
            + "    and (t17.name like concat('%', #{p1.goods_prop,jdbcType=VARCHAR}, '%') or #{p1.goods_prop,jdbcType=VARCHAR} is null or #{p1.goods_prop,jdbcType=VARCHAR} = '')                      "
            + "   <if test='p1.owner_ids != null and p1.owner_ids.length != 0' >                                                                                                                       "
            + "    and t1.owner_id in                                                                                                                                                                  "
            + "        <foreach collection='p1.owner_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                                  "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "   <if test='p1.warehouse_types != null and p1.warehouse_types.length != 0' >                                                                                                           "
            + "    and t2.warehouse_type in                                                                                                                                                            "
            + "        <foreach collection='p1.warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                                            "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "   <if test='p1.isZLEnvironment' >                                                                                                                                                      "
            + "     and t2.warehouse_type != '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_CL+"'                                                                                                                                                  "
            + "   </if>                                                                                                                                                                                "
            +" 	AND exists (  SELECT  1 FROM b_message tt1 where                                                   "
            +"       tt1.serial_type = '"+DictConstant.DICT_SYS_CODE_TYPE_M_INVENTORY_STAGNATION+"'                "
            +"       and tt1.type = "+DictConstant.DICT_B_MESSAGE_TYPE_1+"                                       "
            +"       and t1.id = tt1.serial_id and t1.code = tt1.serial_code)                                       "
            // 仓库权限
            + "     ${p1.params.dataScopeAnnotation}                                                                                                "
            + "  </script>   ")
    int selectExportWarningNum(@Param("p1") MInventoryDetailQueryVo searchCondition);


    /**
     * 中转滞留预警库存明细查询sql
     */
    @Select("  <script>  "
            + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
            + select_query_detail_warning
            + "  where true                                                                                                                  "
            + "    and (CONCAT(t7.name,t7.short_name,t7.name_pinyin,t7.short_name_pinyin) like CONCAT ('%',#{p1.owner_name,jdbcType=VARCHAR},'%') or #{p1.owner_name,jdbcType=VARCHAR} is null)        "
            + "    and (CONCAT(ifnull(t6.name,''),ifnull(t6.spec,''),ifnull(t6.pm,''),ifnull(t9.name,'')) like CONCAT ('%',#{p1.sku_name,jdbcType=VARCHAR},'%') or #{p1.sku_name,jdbcType=VARCHAR} is null)                                        "
            + "    and (concat(t9.code, '_', t6.code) like concat('%', #{p1.sku_code}, '%') or #{p1.sku_code} is null or #{p1.sku_code} = '')                                                        "
            + "   <if test='p1.warehouse_ids != null and p1.warehouse_ids.length!=0' >                                                                                                                 "
            + "    and t1.warehouse_id in                                                                                                                                                              "
            + "        <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                              "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "    and (t1.warehouse_id = #{p1.warehouse_id,jdbcType=INTEGER} or #{p1.warehouse_id,jdbcType=INTEGER} is null)                                                                          "
            + "    and (t1.owner_id = #{p1.owner_id,jdbcType=INTEGER} or #{p1.owner_id,jdbcType=INTEGER} is null)                                                                                      "
            + "    and (t10.name like concat('%', #{p1.category_name,jdbcType=VARCHAR}, '%') or #{p1.category_name,jdbcType=VARCHAR} is null or #{p1.category_name,jdbcType=VARCHAR} = '')             "
            + "    and (t17.name like concat('%', #{p1.goods_prop,jdbcType=VARCHAR}, '%') or #{p1.goods_prop,jdbcType=VARCHAR} is null or #{p1.goods_prop,jdbcType=VARCHAR} = '')             "
            + "   <if test='p1.owner_ids != null and p1.owner_ids.length != 0' >                                                                                                                       "
            + "    and t1.owner_id in                                                                                                                                                                  "
            + "        <foreach collection='p1.owner_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                                  "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "   <if test='p1.warehouse_types != null and p1.warehouse_types.length != 0' >                                                                                                           "
            + "    and t2.warehouse_type in                                                                                                                                                            "
            + "        <foreach collection='p1.warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                                            "
            + "         #{item}                                                                                                                                                                        "
            + "        </foreach>                                                                                                                                                                      "
            + "   </if>                                                                                                                                                                                "
            + "   <if test='p1.isZLEnvironment' >                                                                                                                                                      "
            + "     and t2.warehouse_type != '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_CL+"'                                                                                                                                                  "
            + "   </if>                                                                                                                                                                                "
            +" 	AND exists (  SELECT  1 FROM b_message tt1 where                                                   "
            +"       tt1.serial_type = '"+DictConstant.DICT_SYS_CODE_TYPE_M_INVENTORY_STAGNATION+"'                "
            +"       and tt1.type = "+DictConstant.DICT_B_MESSAGE_TYPE_1+"                                       "
            +"       and t1.id = tt1.serial_id and t1.code = tt1.serial_code)                                       "
            // 仓库权限
            + "     ${p1.params.dataScopeAnnotation}                                                                                                "
            + "  </script>   ")
    List<MInventoryStagnationWarningExportVo> selectExportAllDataWarning(@Param("p1") MInventoryDetailQueryVo searchCondition);
}
